﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for CustomerDAL
/// </summary>
public class CustomerDAL
{
    DataAccess db;
	public CustomerDAL()
	{
        db = new DataAccess();
	}
    public DataTable getAllCustomer()
    {
        return db.executeSelect("SELECT_Customer");
    }
    public int InsertUpdateCustomer(Customer cus)
    {
        SqlParameter[] paraList = new SqlParameter[9];
        paraList[0] = db.AddParameter("customer_id", cus.Customer_id, SqlDbType.Int);
        paraList[1] = db.AddParameter("first_name", cus.First_name, SqlDbType.NVarChar, 32);
        paraList[2] = db.AddParameter("last_name", cus.Last_name, SqlDbType.NVarChar, 32);
        paraList[3] = db.AddParameter("email",cus.Email,SqlDbType.NVarChar,32);
        paraList[4] = db.AddParameter("password", cus.Password, SqlDbType.NVarChar, 32);
        paraList[5] = db.AddParameter("address", cus.Address, SqlDbType.NVarChar, 32);
        paraList[6] = db.AddParameter("city",cus.City,SqlDbType.NVarChar,32);
        paraList[7] = db.AddParameter("phone", cus.Phone, SqlDbType.VarChar, 32);
        paraList[8] = db.AddParameter("customer_group_id",cus.Customer_group_id,SqlDbType.Int);
        return db.executeUpdate("IS_Customer", paraList);
    }
    public DataTable getCustomerByID(int customer_id)
    {
        SqlParameter[] paraList = new SqlParameter[5];
        paraList[0] = db.AddParameter("customer_id", customer_id, SqlDbType.Int);
        paraList[1] = db.AddParameter("first_name", null,SqlDbType.NVarChar,32);
        paraList[2] = db.AddParameter("last_name", null, SqlDbType.NVarChar, 32);
        paraList[3] = db.AddParameter("email", null, SqlDbType.NVarChar, 32);
        paraList[4] = db.AddParameter("customer_group_id", 0, SqlDbType.Int);

        return db.executeSelect("SELECT_Customer", paraList);
    }
    public DataTable getCustomerByFirstName(string Fname)
    {
        SqlParameter[] paraList = new SqlParameter[5];
        paraList[0] = db.AddParameter("customer_id", 0, SqlDbType.Int);
        paraList[1] = db.AddParameter("first_name", Fname, SqlDbType.NVarChar, 32);
        paraList[2] = db.AddParameter("last_name", null, SqlDbType.NVarChar, 32);
        paraList[3] = db.AddParameter("email", null, SqlDbType.NVarChar, 32);
        paraList[4] = db.AddParameter("customer_group_id", 0, SqlDbType.Int);
        return db.executeSelect("SELECT_Customer", paraList);
    }
    public DataTable getCustomerByLastName(string Lname)
    {
        SqlParameter[] paraList = new SqlParameter[5];
        paraList[0] = db.AddParameter("customer_id", 0, SqlDbType.Int);
        paraList[1] = db.AddParameter("first_name", null, SqlDbType.NVarChar, 32);
        paraList[2] = db.AddParameter("last_name", Lname, SqlDbType.NVarChar, 32);
        paraList[3] = db.AddParameter("email", null, SqlDbType.NVarChar, 32);
        paraList[4] = db.AddParameter("customer_group_id", 0, SqlDbType.Int);
        return db.executeSelect("SELECT_Customer", paraList);
    }
    public DataTable getCustomerByEmail(string email)
    {
        SqlParameter[] paraList = new SqlParameter[5];
        paraList[0] = db.AddParameter("customer_id", 0, SqlDbType.Int);
        paraList[1] = db.AddParameter("first_name", null, SqlDbType.NVarChar, 32);
        paraList[2] = db.AddParameter("last_name", null, SqlDbType.NVarChar, 32);
        paraList[3] = db.AddParameter("email", email, SqlDbType.NVarChar, 32);
        paraList[4] = db.AddParameter("customer_group_id", 0, SqlDbType.Int);
        return db.executeSelect("SELECT_Customer", paraList);
    }
    public DataTable getCustomerByCGID(int id)
    {
        SqlParameter[] paraList = new SqlParameter[5];
        paraList[0] = db.AddParameter("customer_id", 0, SqlDbType.Int);
        paraList[1] = db.AddParameter("first_name", null, SqlDbType.NVarChar, 32);
        paraList[2] = db.AddParameter("last_name", null, SqlDbType.NVarChar, 32);
        paraList[3] = db.AddParameter("email", null, SqlDbType.NVarChar, 32);
        paraList[4] = db.AddParameter("customer_group_id", id, SqlDbType.Int);
        return db.executeSelect("SELECT_Customer", paraList);
    }
    public DataTable Login(string email, string pass)
    {
        SqlParameter[] paraList = new SqlParameter[2];
        paraList[0] = db.AddParameter("email", email, SqlDbType.NVarChar, 32);
        paraList[1] = db.AddParameter("password", pass, SqlDbType.VarChar, 32);
        return db.executeSelect("Customer_Login", paraList);
    }
    public int Delete_customer(int customer_id) 
    {
        SqlParameter[] paraList = new SqlParameter[1];
        paraList[0] = db.AddParameter("customer_id", customer_id, SqlDbType.Int);
        return db.executeUpdate("DE_Customer", paraList);
    }
    public int ChangePassword(string email,string passWordOld,string passWordnew)
    {
        string sql = "Update Customer set [password]='" + passWordnew + "'where email= '" + email + "'AND [password]='" + passWordOld+"'";
        SqlConnection conn = db.OpenConnection();
        SqlCommand cmd = new SqlCommand(sql,conn);
        int result = 0;
        try
        {
            result = cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {

            //relationship error
            result = 0;
        }
        db.CloseConnection(conn);
        return result;

    }
}